/*
 * Copyright (c) 2014, Hsu Shih-Chia (schsu01@gmail.com)
 * All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.qood;

import java.util.*;

/**
 * Please run {@link #main(String[])} to see what DB Types, Formulas, and Operators were supported by current version.
 *
 * @param <T> the returns type
 */
abstract class Formula<T> {
  private static final Map<String, Formula> MAP = new LinkedHashMap<String, Formula>();
  final Class<T> type;
  private final String name;
  private final String[] args;

  Formula(String name, Class<T> type, String... args) {
    this.name = name;
    this.type = type;
    this.args = args;
    final Formula old = MAP.put(name.toUpperCase(), this);
    if (null != old) throw new IllegalStateException("Duplicated Formula \"" + old + "\" and \"" + this + '"');
  }

  public static void main(String[] args) {
    System.out.println("\nSupported DB Types:");
    QNew.builderMap(String.class, DBLoader.class).addAll(DBLoader.MAP)
      .toModel("Name", "DBLoader").query().orderBy("Name").result().debug(-1);
    System.out.println("\nSupported Formulas:");
    QNew.builderMap(String.class, Formula.class).addAll(MAP).toModel("Name", "Usage").debug(-1);
    System.out.println("\nSupported Operators:");
    QNew.builderPOJO(Operator.class).method("Name", "name").field("Operator", "name").field("Weight", "priority")
      .field("Sample", "sample").add(Operator.values()).toModel().debug(-1);
  }

  static Formula getInstance(String func) {
    return MAP.get(func.toUpperCase());
  }

  @SuppressWarnings("unchecked")
  static int compare(Object v1, Object v2) {//safe but slow
    return null == v1 ? null == v2 ? 0 : -1 : null == v2 ? 1 :
      v1 instanceof Comparable && v2.getClass() == v1.getClass() ?//slow
        ((Comparable) v1).compareTo(v2) ://use Comparable
        String.valueOf(v1).compareTo(String.valueOf(v2));//use toString()
  }

  @SuppressWarnings("unchecked")
  static int compare(Comparable v1, Comparable v2) {
    return null == v1 ? null == v2 ? 0 : -1 : null == v2 ? 1 : v1.compareTo(v2);
  }

  private static boolean isNumber(Class c) {
    return Number.class.isAssignableFrom(c);
  }

  boolean isAggregate() {
    return false;
  }

  abstract Col createCol(Col... cols);

  @Override
  public String toString() {
    final String ret = null == type ? "?" : type.getSimpleName();
    return ret + ' ' + name + '(' + StringUtil.join(args, ",") + ')';
  }

  private abstract static class ForObject extends ForEval<Comparable> {
    private final int getTypeBy;

    @SuppressWarnings({"NullableProblems"})
    ForObject(String name, int getTypeBy, String... args) {
      super(name, null, args);
      this.getTypeBy = getTypeBy;
    }

    @Override
    final Class getType(int cols, Col[] col) {
      return col[getTypeBy >= 0 ? getTypeBy : cols + getTypeBy].getType();
    }
  }

  private abstract static class ForComp extends ForEval<Boolean> {
    private static final String[] ARGS = {"Value1", "Value2", "..."};

    ForComp(String name) {
      super(name, Boolean.class, ARGS);
    }

    final Boolean eval(int row, int cols, Col[] col) {
      final Object first = col[0].getRow(row);
      for (int i = 1; i < cols; i++) if (notMatch(compare(first, col[i].getRow(row)))) return false;
      return true;
    }

    abstract boolean notMatch(int comp);
  }

  static {
    //no cache
    new Formula<Integer>("ROWID", Integer.class) {
      @SuppressWarnings({"NullableProblems"})
      Col createCol(Col[] cols) {
        return Col.getId(null);
      }
    };
    //const
    new ForConst<String>("NULL", String.class) {
      String eval(Col[] col) {
        return null;
      }
    };
    new ForConst<Double>("NA", Double.class) {
      Double eval(Col[] col) {
        return null;
      }
    };
    new ForConst<Double>("PI", Double.class) {
      Double eval(Col[] col) {
        return Math.PI;
      }
    };
    new ForConst<Date>("NOW", Date.class) {
      Date eval(Col[] col) {
        return new Date();
      }
    };
    //object
    new ForObject("NVL", 1, "ChkValue", "ResultIfNull", "[ResultElse=ChkValue]") {
      Comparable eval(int row, int cols, Col[] col) {
        final Object value = col[0].getRow(row);
        return (Comparable) (null == value ? col[1].getRow(row) : cols > 2 ? col[2].getRow(row) : value);
      }
    };
    new ForObject("IIF", 1, "ChkValue", "ResultIfTrue", "[ResultElse=null]") {
      Comparable eval(int row, int cols, Col[] col) {
        return (Comparable) (TypeUtil.toBoolean(col[0].getRow(row)) ?
          col[1].getRow(row) : cols > 2 ? col[2].getRow(row) : null);
      }
    };
    new ForObject("CHOOSE", -1, "ChkValue", "[IfVal]", "[ThenVal]", "...", "[ResultElse=null]") {
      Comparable eval(int row, int cols, Col[] col) {
        final Object value = col[0].getRow(row);
        final int n = cols - 1;
        if (null == value) for (int i = 1; i < n; i += 2) {
          if (null == col[i].getRow(row)) return (Comparable) col[i + 1].getRow(row);
        }
        else for (int i = 1; i < n; i += 2)
          if (value.equals(col[i].getRow(row))) return (Comparable) col[i + 1].getRow(row);
        return 0 == (cols & 1) ? (Comparable) col[n].getRow(row) : null;
      }
    };
    /*MIN(Value1,[Values2,...])*/
    /*MAX(Value1,[Values2,...])*/
    /*DECODE(ChkValue,IfVal1,ThenVal1,[IfVal2,ThenVal2]...,[ResultElse=ChkValue])*/
    //Boolean
    new ForComp(">=") {
      boolean notMatch(int comp) {
        return comp < 0;
      }
    };
    new ForComp("<=") {
      boolean notMatch(int comp) {
        return comp > 0;
      }
    };
    new ForComp(">") {
      boolean notMatch(int comp) {
        return comp <= 0;
      }
    };
    new ForComp("<") {
      boolean notMatch(int comp) {
        return comp >= 0;
      }
    };
    new ForComp("==") {
      boolean notMatch(int comp) {
        return comp != 0;
      }
    };
    new ForComp("!=") {
      boolean notMatch(int comp) {
        return comp == 0;
      }
    };
    //int
    new ForEval<Integer>("INT", Integer.class, "Value") {
      Integer eval(int row, int cols, Col[] col) {
        final double value = TypeUtil.toDouble(col[0].getRow(row));
        return Double.isNaN(value) ? null : (int) value;
      }
    };
    new ForEval<Integer>("INSTR", Integer.class, "Text", "Pattern") {
      private final Integer NONE = -1;//Integer.

      Integer eval(int row, int cols, Col[] col) {
        Object text = col[0].getRow(row), pattern = col[1].getRow(row);
        if (null == text || null == pattern) return NONE;
        final int result = String.valueOf(text).indexOf(String.valueOf(pattern));
        return result < 0 ? NONE : result;
      }
    };
    /*YEAR([DateTime])*/
    /*MONTH([DateTime])*/
    /*DAY([DateTime])*/
    /*HOUR([DateTime])*/
    /*MINUTE([DateTime])*/
    /*SECOND([DateTime])*/
    /*WEEK([DateTime],[FirstDayOfWeek])*/
    //double
    new ForEval<Double>("NUM", Double.class, "Value") {
      Double eval(int row, int cols, Col[] col) {
        return TypeUtil.toDouble(col[0].getRow(row));
      }
    };
    new ForEval<Double>("ABS", Double.class, "Value") {
      Double eval(int row, int cols, Col[] col) {
        final double value = TypeUtil.toDouble(col[0].getRow(row));
        return value < 0 ? -value : value;
      }
    };
    new ForEval<Double>("+", Double.class, "[Value=0]", "...") {
      Double eval(int row, int cols, Col[] col) {
        double result = 0;
        for (int i = 0; i < cols; i++) result += TypeUtil.toDouble(col[i].getRow(row));
        return result;
      }
    };
    new ForEval<Double>("-", Double.class, "[Value=0]") {
      Double eval(int row, int cols, Col[] col) {
        double result = 0;
        for (int i = 0; i < cols; i++) result -= TypeUtil.toDouble(col[i].getRow(row));
        return result;
      }
    };
    new ForEval<Double>("*", Double.class, "[Value=1]", "...") {
      Double eval(int row, int cols, Col[] col) {
        double result = 1;
        for (int i = 0; i < cols; i++) if (0 == (result *= TypeUtil.toDouble(col[i].getRow(row)))) break;
        return result;
      }
    };
    new ForEval<Double>("1/", Double.class, "[Value=1]", "...") {
      Double eval(int row, int cols, Col[] col) {
        double result = 1;
        for (int i = 0; i < cols; i++) {
          final double value = TypeUtil.toDouble(col[i].getRow(row));
          if (0 == value) return null;//or NaN?
          result /= value;
        }
        return result;
      }
    };
    new ForEval<Double>("SQRT", Double.class, "Value") {
      Double eval(int row, int cols, Col[] col) {
        return Math.sqrt(TypeUtil.toDouble(col[0].getRow(row)));
      }
    };
    new ForEval<Double>("LOG", Double.class, "Value", "[Base=Exp()]") {
      Double eval(int row, int cols, Col[] col) {
        final double result = Math.log(TypeUtil.toDouble(col[0].getRow(row)));
        return 1 == cols ? result : result / Math.log(TypeUtil.toDouble(col[1].getRow(row)));
      }
    };
    new ForEval<Double>("MOD", Double.class, "Value", "Divisor") {
      Double eval(int row, int cols, Col[] col) {
        final double value = TypeUtil.toDouble(col[0].getRow(row));
        return value % TypeUtil.toDouble(col[1].getRow(row));
      }
    };
    new ForEval<Double>("POW", Double.class, "Value", "Power") {
      Double eval(int row, int cols, Col[] col) {
        final double value = TypeUtil.toDouble(col[0].getRow(row));
        return Math.pow(value, TypeUtil.toDouble(col[1].getRow(row)));
      }
    };
    new ForEval<Double>("SQSUM", Double.class, "[Value=0]", "...") {
      Double eval(int row, int cols, Col[] col) {
        double result = 0;
        for (int i = 0; i < cols; i++) {
          final double value = TypeUtil.toDouble(col[i].getRow(row));
          if (Double.isNaN(value)) return null;//or continue?
          result += (value * value);
        }
        return result;
      }
    };
    /*Avg([NumValues=0,...]*/
    /*Exp([NumValue=1])*/
    /*Ceil(NumValue)*/
    /*Floor(NumValue)*/
    /*Round(NumValue,[Precise])*/
    //long
    new ForEval<Long>("DATEVALUE", Long.class, "Date") {
      Long eval(int row, int cols, Col[] col) {
        final long mi = TypeUtil.toDateValue(col[0].getRow(row));
        return mi < 0 ? null : mi;
      }
    };
    //date
    new ForEval<Date>("TO_DATE", Date.class, "Text") {//before: TO_DATE(Date,[Format])
      Date eval(int row, int cols, Col[] col) {
        return TypeUtil.toDate(col[0].getRow(row));
      }
    };
    /*DATE_SHIFT(DateTime,YearNum,MonthNum,DayNum,HourNum,MinuteNum,SecondNum)*/
    /*DATE_SET(DateTime,YearNum,MonthNum,DayNum,HourNum,MinuteNum,SecondNum)*/
    /*ADD_YEARS(Date,Number)*/
    /*ADD_MONTHS(Date,Number)*/
    /*ADD_DAYS(Date,Number)*/
    /*ADD_HOURS(Date,Number)*/
    /*ADD_MINS(Date,Number)*/
    /*ADD_SECS(Date,Number)*/
    /*REMOVE_TIME(Date)*/
    //string
    new ForEval<String>("S+BOOL", String.class, "[Value='F']") {
      String eval(int row, int cols, Col[] col) {
        return cols > 0 && TypeUtil.toBoolean(col[0].getRow(row)) ? "T" : "F";
      }
    };
    new ForEval<String>("SUBSTR", String.class, "Value", "BeginIndex", "[EndIndex]") {
      String eval(int row, int cols, Col[] col) {
        final Object value = col[0].getRow(row);
        if (null == value) return null;
        final String text = value.toString();
        final int n = text.length();
        int a = TypeUtil.toInt(col[1].getRow(row)), b = cols > 2 ? TypeUtil.toInt(col[2].getRow(row)) : n;
        if (a < 0) a += n;
        if (b < 0) b += n;
        if (a > b) {
          int c = a;
          a = b;
          b = c;
        }//swap values
        return a < 0 || a > n ? null : text.substring(a, b > n ? n : b);
      }
    };
    new ForEval<String>("CONCAT", String.class, "[Value=null]", "...") {
      String eval(int row, int cols, Col[] col) {
        final StringBuilder sb = new StringBuilder();
        for (int i = 0; i < cols; i++) {
          final Object value = col[i].getRow(row);
          if (null != value) sb.append(value);
        }
        return sb.length() > 0 ? sb.toString() : null;
      }
    };
    new ForEval<String>("JOIN", String.class, "[Value=null]", "...", "Seperator") {
      String eval(int row, int cols, Col[] col) {
        final int n = cols - 1;
        final char[] seperator = String.valueOf(col[n].getRow(row)).toCharArray();
        final StringBuilder sb = new StringBuilder();
        for (int i = 0; i < n; i++) {
          final Object value = col[i].getRow(row);
          if (null == value) continue;
          if (sb.length() > 0) sb.append(seperator);
          sb.append(value);
        }
        return sb.length() > 0 ? sb.toString() : null;
      }
    };
    new ForEval<String>("NUMFORMAT", String.class, "Value", "[StrPattern='0.###']") {
      String eval(int row, int cols, Col[] col) {
        final double v = TypeUtil.toDouble(col[0].getRow(row));
        final String p = cols > 1 ? String.valueOf(col[1].getRow(row)) : "0.###";
        return TypeUtil.toString(v, p);
      }
    };
    /*ASCII([AsciiCodes,'String',...])*/
    /*DateTime([DateTime=NOW()])*/
    /*Date([DateTime=NOW()])*/
    /*DateMinute([DateTime=NOW()])"*/
    /*Time([DateTime=NOW()])*/
    /*Weekday([DateTime=NOW()],[Type='Z'|'F'|'S'|'V'])*/
    /*Upper(Value)*/
    /*Lower(Value)*/
    /*Trim(Value)*/
    /*Dump(Value)*/
    /*Replace(Value,SearchText,[ReplaceText=''])*/
    /*Replace_Each(Text,SearchText1,[ReplaceText1='',SearchText2,ReplaceText2,...])*/
    /*REPLACE_SP_CHAR(Text,[ReplaceText=''])*/
    /*Encrypt(PlainText,[EncryptKey])*/
    /*Decrypt(EncryptedText,[DecryptKey])*/
    /*ABBR(LongText,[offset=0],maxLength>=4)*/
    /*Int2Hex(value)*/
    /*Pad_R(Text,Size,[PadChar=' '])*/
    /*PAD_L(Text,Size,[PadChar=' '])*/
    /*PAD_C(Text,Size,[PadChar=' '])*/
    /*Join_NND(Value1,[Value2,...],Seperator)*/
    //aggregate
    new ForGroup<Integer>("GROUPID", Integer.class) {
      C<Integer> createCol(Col[] cols) {
        return new CInt.ID();
      }
    };
    new ForGroup<Integer>("COUNT", Integer.class, "[Value=*]") {
      C<Integer> createCol(Col[] cols) {
        final Col target = 0 == cols.length ? null : cols[0];
        return null == target || target.isConstant() ? new CInt.SIZE() :
          isNumber(target.getType()) ? new CRange.SIZE(target) : new CArray.SIZE(target, SUM_ARRAY_NN);
      }
    };
    new ForGroup<Integer>("COUNT_NND", Integer.class, "Value") {
      C<Integer> createCol(Col[] cols) {
        return new CArray.SIZE(cols[0], SUM_ARRAY_NND);
      }
    };
    new ForGroup<String>("CONCAT_ALL", String.class, "Value", "[Separator=',']") {
      C<String> createCol(Col[] cols) {
        return new CInt.JOIN(cols);
      }
    };
    new ForGroup<String>("CONCAT_NN", String.class, "Value", "[Separator=',']") {
      C<String> createCol(Col[] cols) {
        return new CArray.JOIN(cols, SUM_ARRAY_NN);
      }
    };
    new ForGroup<String>("CONCAT_NND", String.class, "Value", "[Separator=',']") {
      C<String> createCol(Col[] cols) {
        return new CArray.JOIN(cols, SUM_ARRAY_NND);
      }
    };
    new ForGroup<String>("CONCAT_NNDS", String.class, "Value", "[Separator=',']") {
      C<String> createCol(Col[] cols) {
        return new CArray.JOIN(cols, SUM_ARRAY_NNDS);
      }
    };
    new ForGroup<Object>("FIRST", null, "Value") {
      C<?> createCol(final Col[] cols) {
        return new CInt.FIRST(cols[0], 0/*n+0*/);
      }
    };
    new ForGroup<Object>("LAST", null, "Value") {
      C<?> createCol(final Col[] cols) {
        return new CInt.LAST(cols[0], 1/*n-1*/);
      }
    };
    new ForGroup<Object>("MIN", null, "Value") {
      C<?> createCol(Col[] cols) {
        final Col target = cols[0];
        final Class type = target.getType();
        return isNumber(type) ? new CRange.MIN(target) : new CArray.FIRST(target, type);
      }
    };
    new ForGroup<Object>("MAX", null, "Value") {
      C<?> createCol(Col[] cols) {
        final Col target = cols[0];
        final Class type = target.getType();
        return isNumber(type) ? new CRange.MAX(target) : new CArray.LAST(target, type);
      }
    };
    new ForGroup<Double>("RANGE", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CRange.RANGE(cols[0]);
      }
    };
    new ForGroup<Double>("SUM", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CEasy.SUM(cols[0]);
      }
    };
    new ForGroup<Double>("MEAN", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CEasy.MEAN(cols[0]);
      }
    };
    new ForGroup<Double>("STDDEV", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CEasy.STDDEV(cols[0]);
      }
    };
    new ForGroup<Double>("VARIANCE", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CEasy.VAR(cols[0]);
      }
    };
    new ForGroup<Double>("SUMSQ", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CEasy.SUMSQ(cols[0]);
      }
    };
    new ForGroup<Double>("GEOMEAN", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CHard.GEOMEAN(cols[0]);
      }
    };
    new ForGroup<Double>("MEDIAN", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CHard.MEDIAN(cols[0]);
      }
    };
    new ForGroup<Double>("Q1", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CHard.PERCENTILE(cols[0], 25);
      }
    };
    new ForGroup<Double>("Q3", Double.class, "Value") {
      C<Double> createCol(Col[] cols) {
        return new CHard.PERCENTILE(cols[0], 75);
      }
    };
    new ForGroup<Double>("PERCENTILE", Double.class, "Value", "Percentile=0~100") {
      C<Double> createCol(Col[] cols) {
        return new CHard.PERCENTILE(cols[0], TypeUtil.toInt(cols[1].getRow(0)));
      }
    };
    System.out.println("Loaded Formulas: " + MAP.keySet());
  }
}

enum Operator {
  CONCAT("|| ", "3|| 2 ='32'", 1, "CONCAT"),
  SUM("+ ", "3+ 2 =5", 2, "+"),
  MINUS("- ", "3- 2 =1", 2, "-", "+"),
  MULTIPLY("* ", "3* 2 =6", 3, "*"),
  DIVIDE("/ ", "3/ 2 =1.5", 3, "1/", "*"),
  MODE("% ", "3% 2 =1", 3, "MOD"),
  POWER("^ ", "3^ 2 =9", 4, "POW");
  private final String name, sample;
  private final int priority;
  private final Formula[] fn;

  private Operator(String name, String sample, int priority, String... func) {
    this.name = name;
    this.sample = sample;
    this.priority = priority;
    final int funcs = func.length;
    final Formula[] fn = new Formula[funcs];
    for (int i = 0; i < funcs; i++)
      if (null == (fn[i] = Formula.getInstance(func[i])))
        throw new IllegalArgumentException("Undefined function:\"" + func[i] + "\"");
    this.fn = fn;
  }

  private static Operator getPossible(char c) {
    switch (c) {
      case '+':
        return SUM;
      case '-':
        return MINUS;
      case '*':
        return MULTIPLY;
      case '/':
        return DIVIDE;
      case '^':
        return POWER;
      case '%':
        return MODE;
      case '|':
        return CONCAT;
      default:
        return null;
    }
  }

  static Operator getOperator(char c, String s, int i) {
    final Operator op = getPossible(c);//match first char
    if (null == op) return null;
    final String name = op.name;
    final int n = name.length();
    if (s.length() < i + n) return null;
    for (int j = 1; j < n; j++) if (name.charAt(j) != s.charAt(i + j)) return null;
    return op;
  }

  void merge(Deque<Col> output) {
    final Col p1 = output.pollLast(), p2 = output.pollLast();
    output.add(2 == fn.length ? fn[1].createCol(p2, fn[0].createCol(p1)) : fn[0].createCol(p2, p1));
  }

  int next(Deque<Operator> ops, Deque<Col> temp, int i) {
    for (final int p = priority; !ops.isEmpty() && ops.getLast().priority >= p; ) ops.pollLast().merge(temp);
    ops.add(this);
    return i + name.length() - 1;
  }
}

abstract class ForConst<T extends Comparable> extends Formula<T> {
  ForConst(String name, Class<T> type, String... args) {
    super(name, type, args);
  }

  final Col createCol(Col[] cols) {
    return Col.getConst(type, eval(cols));
  }

  abstract T eval(Col[] col);
}

abstract class ForEval<T extends Comparable> extends Formula<T> {
  @SuppressWarnings({"unchecked"})
  ForEval(String name, Class<T> type, String... args) {
    super(name, type, args);
  }

  private static boolean isAllConst(Col[] cols) {
    for (final Col col : cols) if (!col.isConstant()) return false;
    return true;
  }

  abstract T eval(int row, int cols, Col[] col);

  Class getType(int cols, Col[] col) {
    return type;//overridable
  }

  final Col createCol(Col[] cols) {
    return isAllConst(cols) ? Col.getConst(type, eval(0, cols.length, cols)) ://pre-evaluate
      new C(cols, getType(cols.length, cols));
  }

  private final class C extends Col implements CacheList.Loader<Comparable> {
    private final Class type;
    private final int cols;
    private final Col[] col;
    private final CacheList<Comparable> cache;

    C(Col[] col, Class type) {
      this.type = type;
      this.cols = null == col ? 0 : col.length;
      this.col = col;
      this.cache = new CacheList<Comparable>(this);
    }

    Object getRow(int row) {
      return cache.get(row);
    }

    public Comparable loadForCache(int row) {
      return eval(row, cols, col);
    }

    Class getType() {
      return type;
    }

    int expectSize() {
      int maxSize = -1;
      for (int i = 0; i < cols; i++) {//for each cols
        final int size = col[i].expectSize();
        if (size > maxSize) maxSize = size;
      }
      return maxSize;
    }

    @Override
    boolean isAggregate() {
      for (final Col c : col) if (c.isAggregate()) return true;
      return false;
    }

    @Override
    int compare(int row1, int row2) {
      return Formula.compare(cache.get(row1), cache.get(row2));
    }
  }
}

@SuppressWarnings({"unchecked", "NullableProblems"})
abstract class ForGroup<T> extends Formula<T> {
  static final int SUM_INT_READ = 0;
  static final int SUM_STAT_RANGE = 1;
  static final int SUM_STAT_EASY = 2;//> SUM_STAT_RANGE
  static final int SUM_STAT_HARD = 3;//> SUM_STAT_EASY
  static final int SUM_ARRAY_MINAX = 4;
  static final int SUM_ARRAY_NNDS = 5;//> SUM_ARRAY_MINAX
  static final int SUM_ARRAY_NN = 6;
  static final int SUM_ARRAY_NND = 7;
  static final int[] SUM = {SUM_INT_READ, SUM_STAT_RANGE, SUM_STAT_EASY, SUM_STAT_HARD,
    SUM_ARRAY_MINAX, SUM_ARRAY_NNDS, SUM_ARRAY_NN, SUM_ARRAY_NND};

  ForGroup(String name, Class<T> type, String... args) {
    super(name, type, args);
  }

  abstract C<? extends T> createCol(Col[] cols);

  @Override
  final boolean isAggregate() {
    return true;
  }

  static final class Summary {
    private final Col target;
    private final Collection[] results;
    private final Task[] tasks;

    Summary(Col target, Collection[] results, Collection<Task> tasks) {
      this.target = target;
      this.results = results;
      this.tasks = tasks.toArray(new Task[tasks.size()]);
      tasks.clear();
    }

    public static Summary[] getInstance(Map<Col, Collection[]> aggMap, IntRead[] groupRows) {
      final int groups = groupRows.length;
      final Collection firstResult = Arrays.asList(groupRows);
      final Collection<Summary> result = new ArrayList<Summary>(aggMap.size());
      final Collection<Task> tasks = new ArrayList<Task>(SUM.length);
      for (final Map.Entry<Col, Collection[]> entry : aggMap.entrySet()) {
        final Collection[] results = entry.getValue();
        results[SUM_INT_READ] = firstResult;//set anyway
        if (null != results[SUM_STAT_HARD]) tasks.add(new Stat(groups, SUM_STAT_HARD));
        else if (null != results[SUM_STAT_EASY]) tasks.add(new Stat(groups, SUM_STAT_EASY));
        else if (null != results[SUM_STAT_RANGE]) tasks.add(new Stat(groups, SUM_STAT_RANGE));
        if (null != results[SUM_ARRAY_NNDS]) tasks.add(new Array(groups, SUM_ARRAY_NNDS, SUM_ARRAY_MINAX));
        else if (null != results[SUM_ARRAY_MINAX]) tasks.add(new Array(groups, SUM_ARRAY_MINAX));
        if (null != results[SUM_ARRAY_NND]) tasks.add(new Array(groups, SUM_ARRAY_NND));
        if (null != results[SUM_ARRAY_NN]) tasks.add(new Array(groups, SUM_ARRAY_NN));
        if (!tasks.isEmpty()) result.add(new Summary(entry.getKey(), results, tasks));
      }
      return result.toArray(new Summary[result.size()]);
    }

    void sum(int group, int row) {
      final Object value = target.getRow(row);
      for (final Task task : tasks) task.sum(group, value);
    }

    void done() {
      for (final Task task : tasks) task.done(results);
    }

    private static abstract class Task<T> {
      final int type;
      final T[] result;

      private Task(int type, int groups) {
        this.type = type;
        this.result = init(groups);
      }

      void sum(int group, Object value) {
        sum(result[group], value);
      }

      abstract T[] init(int groups);

      abstract void sum(T result, Object value);

      abstract void done(Collection[] results);
    }

    private static final class Stat extends Task<IStat.Add> {
      private Stat(int groups, int type) {
        super(type, groups);
      }

      IStat.Add[] init(int groups) {
        final IStat.Add[] result = new IStat.Add[groups];
        for (int i = 0; i < groups; i++)
          result[i] = SUM_STAT_HARD == type ? org.qood.Stat.ofHard() :
            SUM_STAT_EASY == type ? org.qood.Stat.ofEasy() : org.qood.Stat.ofRange();
        return result;
      }

      void sum(IStat.Add result, Object value) {
        result.add(TypeUtil.toDouble(value));
      }

      void done(Collection[] results) {
        results[SUM_STAT_HARD] = results[SUM_STAT_EASY] = results[SUM_STAT_RANGE] = Arrays.asList(result);
      }
    }

    private static final class Array extends Task<Collection> {
      private final int[] types;

      private Array(int groups, int type, int... types) {
        super(type, groups);
        this.types = types;
      }

      Object[] toArray(Collection c) {
        final Object[] result = c.toArray();
        if (SUM_ARRAY_NNDS == type) Arrays.sort(result);
        return result;
      }

      Collection[] init(int groups) {
        final Collection[] result = new Collection[groups];
        for (int i = 0; i < groups; i++)
          result[i] = SUM_ARRAY_MINAX == type ? new MinMax() :
            SUM_ARRAY_NN == type ? new ArrayList() : new LinkedHashSet();
        return result;
      }

      void sum(Collection result, Object value) {
        if (null != value) result.add(value);
      }

      void done(Collection[] results) {
        final int groups = result.length;
        final Object[][] array = new Object[groups][];
        for (int i = 0; i < groups; i++) array[i] = toArray(result[i]);
        final Collection result = results[type] = Arrays.asList(array);
        for (final int type : types) results[type] = result;
      }
    }
  }

  abstract static class C<T> extends Col implements CacheList.Loader<T> {//used in Temp.class
    final Col target;
    private final int sum;
    private final Class type;
    private transient CacheList<T> cache;

    C(Col target, int sum, Class type) {
      this.sum = sum;
      this.type = type;
      this.target = target;
    }

    @Override
    final boolean isAggregate() {
      return true;
    }

    final Class getType() {
      return type;
    }

    @Override
    @SuppressWarnings("unchecked")
    final int compare(int row1, int row2) {
      return Formula.compare((Comparable) cache.get(row1), (Comparable) cache.get(row2));
    }

    final T getRow(int row) {
      return cache.get(row);
    }

    final int expectSize() {
      return cache.size();
    }

    abstract void submit(Collection result);

    void submit(boolean init, Map<Col, Collection[]> aggMap) {
      Collection[] results = aggMap.get(target);
      if (init) {
        if (null == results) aggMap.put(target, results = new Collection[SUM.length]);
        results[sum] = Collections.EMPTY_LIST;//register
      } else {
        final Collection result = results[sum];
        cache = new CacheList<T>(this, result.size());
        submit(result);
      }
    }
  }

  abstract static class CInt<T> extends C<T> {//COUNT,FIRST,LAST
    IntRead[] result;

    CInt(Col target, Class type) {
      super(target, SUM_INT_READ, type);
    }

    void submit(Collection result) {
      this.result = (IntRead[]) result.toArray(new IntRead[result.size()]);
    }

    static final class ID extends CInt<Integer> {
      ID() {
        super(null, Integer.class);
      }

      public Integer loadForCache(int index) {
        return index;
      }
    }

    static final class SIZE extends CInt<Integer> {
      SIZE() {
        super(null, Integer.class);
      }

      public Integer loadForCache(int index) {
        return result[index].size();
      }
    }

    static final class FIRST extends CInt<Object> {
      private final int n;

      FIRST(Col target, int n) {
        super(target, target.getType());
        this.n = n;
      }

      public Object loadForCache(int index) {
        return target.getRow(result[index].get(n));
      }
    }

    static final class LAST extends CInt<Object> {
      private final int n;

      LAST(Col target, int n) {
        super(target, target.getType());
        this.n = n;
      }

      public Object loadForCache(int index) {
        return target.getRow(result[index].getLast(n));
      }
    }

    static final class JOIN extends CInt<String> {
      final String sep;

      JOIN(Col[] cols) {
        super(cols[0], String.class);
        this.sep = 1 == cols.length ? "," : String.valueOf(cols[1].getRow(0));
      }

      public String loadForCache(int index) {
        final IntRead row = result[index];
        final StringBuilder sb = new StringBuilder().append(target.getRow(row.get(0)));
        for (int i = 1, n = row.size(); i < n; i++) sb.append(sep).append(target.getRow(row.get(i)));
        return sb.toString();
      }
    }
  }

  abstract static class CRange<T extends Number> extends C<T> {
    IStat[] result;

    CRange(Col target, Class type) {
      super(target, SUM_STAT_RANGE, type);
    }

    void submit(Collection result) {
      this.result = (IStat[]) result.toArray(new IStat[result.size()]);
    }

    static final class SIZE extends CRange<Integer> {
      SIZE(Col target) {
        super(target, Integer.class);
      }

      public Integer loadForCache(int index) {
        return result[index].size();
      }
    }

    static final class MIN extends CRange<Double> {
      MIN(Col target) {
        super(target, Double.class);
      }

      public Double loadForCache(int index) {
        return result[index].min();
      }
    }

    static final class MAX extends CRange<Double> {
      MAX(Col target) {
        super(target, Double.class);
      }

      public Double loadForCache(int index) {
        return result[index].max();
      }
    }

    static final class RANGE extends CRange<Double> {
      RANGE(Col target) {
        super(target, Double.class);
      }

      public Double loadForCache(int index) {
        return result[index].range();
      }
    }
  }

  abstract static class CEasy extends C<Double> {
    IStat.Easy[] result;

    CEasy(Col target) {
      super(target, SUM_STAT_EASY, Double.class);
    }

    void submit(Collection result) {
      this.result = (IStat.Easy[]) result.toArray(new IStat.Easy[result.size()]);
    }

    static final class SUM extends CEasy {
      SUM(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].sum();
      }
    }

    static final class MEAN extends CEasy {
      MEAN(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].mean();
      }
    }

    static final class VAR extends CEasy {
      VAR(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].variance();
      }
    }

    static final class STDDEV extends CEasy {
      STDDEV(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].stdDev();
      }
    }

    static final class SUMSQ extends CEasy {
      SUMSQ(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].sumSq();
      }
    }
  }

  abstract static class CHard extends C<Double> {
    IStat.Hard[] result;

    CHard(Col target) {
      super(target, SUM_STAT_HARD, Double.class);
    }

    void submit(Collection result) {
      this.result = (IStat.Hard[]) result.toArray(new IStat.Hard[result.size()]);
    }

    static final class GEOMEAN extends CHard {
      GEOMEAN(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].geoMean();
      }
    }

    static final class MEDIAN extends CHard {
      MEDIAN(Col target) {
        super(target);
      }

      public Double loadForCache(int index) {
        return result[index].median();
      }
    }

    static final class PERCENTILE extends CHard {
      private final int percent;

      PERCENTILE(Col target, int percent) {
        super(target);
        this.percent = percent;
      }

      public Double loadForCache(int index) {
        return result[index].percentile(percent);
      }
    }
  }

  abstract static class CArray<T> extends C<T> {
    Object[][] result;

    CArray(Col target, int sum, Class type) {
      super(target, sum, type);
    }

    void submit(Collection result) {
      this.result = (Object[][]) result.toArray(new Object[result.size()][]);
    }

    static final class FIRST extends CArray<Object> {
      FIRST(Col target, Class type) {
        super(target, SUM_ARRAY_MINAX, type);
      }

      public Object loadForCache(int index) {
        return result[index][0];
      }
    }

    static final class LAST extends CArray<Object> {
      LAST(Col target, Class type) {
        super(target, SUM_ARRAY_MINAX, type);
      }

      public Object loadForCache(int index) {
        final Object[] array = result[index];
        return array[array.length - 1];
      }
    }

    static final class SIZE extends CArray<Integer> {
      SIZE(Col target, int sum) {
        super(target, sum, Integer.class);
      }

      public Integer loadForCache(int index) {
        return result[index].length;
      }
    }

    static final class JOIN extends CArray<String> {
      final String sep;

      JOIN(Col[] cols, int sum) {
        super(cols[0], sum, String.class);
        this.sep = 1 == cols.length ? "," : String.valueOf(cols[1].getRow(0));
      }

      public String loadForCache(int index) {
        return StringUtil.join(result[index], sep).toString();
      }
    }
  }
}

abstract class Stat implements IStat.Easy {
  public static StatHard ofHard() {
    return new StatHard();
  }

  public static StatEasy ofEasy() {
    return new StatEasy();
  }

  public static Range ofRange() {
    return new Range();
  }

  public final double variance() {
    final int n = size();
    if (n <= 1) return Double.NaN;
    final double sum = sum();
    return (sumSq() - sum * sum / n) / (n - 1);
  }

  public final double stdDev() {
    return Math.sqrt(variance());
  }

  public final double mean() {
    return sum() / (double) size();
  }

  public final double range() {
    return max() - min();
  }

  public String toString() {
    return getClass().getName() + " of {n:" + size() + ",sum:" + sum() + ",range:" + range() + ",mean:" + mean() + ",stdDev:" + stdDev() + "}";
  }

  public static final class StatHard extends StatDouble implements IStat.Hard, IStat.Add {
    private final DoubleList _values = new DoubleList();
    private transient double[] _sorted;

    private static double estimate(double lower, double upper, double diffRate) {
      return (upper - lower) * diffRate + lower;
    }

    void addValue(double value) {
      _values.add(value);
      super.addValue(value);
    }

    public void add(double value) {
      if (!Double.isNaN(value)) addValue(value);
    }

    public StatHard add(Hard statHard) {
      if (statHard instanceof StatHard) {
        _values.addAll(((StatHard) statHard)._values.toArray());
        _sum += statHard.sum();
        _sumSq += statHard.sumSq();
        if (_max < statHard.max()) _max = statHard.max();
        if (_min > statHard.min()) _min = statHard.min();
      } else throw new IllegalArgumentException(statHard + " unknown?");
      return this;
    }

    @Override
    public void clear() {
      _values.clear();
      _sorted = null;
      super.clear();
    }

    public double percentile(double p) {
      final int n = size();
      if (0 == n) return Double.NaN;
      final double pos = p * (n + 1);
      final int iPos = (int) pos;
      final double[] sort = sorted();
      return pos < 1 ? sort[0] : pos >= n ? sort[n - 1] : estimate(sort[iPos - 1], sort[iPos], pos - iPos);
    }

    public double percentile(int p) {
      final int n = size();
      if (0 == n) return Double.NaN;
      final double pos = p * (n + 1) / 100d;
//      final double pos = p * (n - 1) / 100d;//excel
      final int iPos = (int) pos;
      final double[] sort = sorted();
      return pos < 1 ? sort[0] : pos >= n ? sort[n - 1] : estimate(sort[iPos - 1], sort[iPos], pos - iPos);
//      return pos <= 0 ? sort[0] : pos >= n ? sort[n - 1] : estimate(sort[iPos], sort[iPos + 1], pos - iPos);//excel
    }

    public double median() {
      final int n = size(), pos = n >> 1;
      if (0 == n) return Double.NaN;
      final double[] sort = sorted();
      return (n & 1) > 0 ? sort[pos] : (sort[pos] + sort[pos - 1]) / 2d;
    }

    public double geoMean() {
      if (_values.isEmpty()) return Double.NaN;
      final double[] sorted = sorted();
      double sumLog = 0;
      for (final double value : sorted) {
        if (0 == value) return Double.NaN;
        sumLog += Math.log(value);
      }
      return Math.exp(sumLog / (double) sorted.length);
    }

    public double[] sorted() {
      final int n = size();
      if (null == _sorted || _sorted.length != n) {
        _sorted = _values.toArray();
        Arrays.sort(_sorted);
      }
      return _sorted;
    }

    public double[] normalized() {
      final double range = range();
      if (range <= 0) return null;
      final double[] result = _values.toArray();
      for (int i = 0, n = result.length; i < n; i++) result[i] = (result[i] - _min) / range;
      return result;
    }

    public int size() {
      return _values.size();
    }
  }

  public static final class StatEasy extends StatDouble implements IStat.Easy, IStat.Add {
    private int n = 0;

    public void clear() {
      n = 0;
      super.clear();
    }

    @Override
    void addValue(double value) {
      n++;
      super.addValue(value);
    }

    public void add(double value) {
      if (!Double.isNaN(value)) addValue(value);
    }

    public void add(Easy statEasy) {
      n += statEasy.size();
      _sum += statEasy.sum();
      _sumSq += statEasy.sumSq();
      if (_max < statEasy.max()) _max = statEasy.max();
      if (_min > statEasy.min()) _min = statEasy.min();
    }

    public int size() {
      return n;
    }
  }

  public static final class Range implements IStat, Add {
    private int n;
    private double min, max;

    private Range() {
      clear();
    }

    private void addValue(double value) {
      if (Double.isNaN(value)) return;
      n++;
      if (max < value) max = value;
      if (min > value) min = value;
    }

    public void add(double value) {
      addValue(value);
    }

    public void add(double... values) {
      for (final double value : values) addValue(value);
    }

    public void clear() {
      n = 0;
      min = Double.POSITIVE_INFINITY;
      max = Double.NEGATIVE_INFINITY;
    }

    public int size() {
      return n;
    }

    public double min() {
      return 0 == n ? Double.NaN : min;
    }

    public double max() {
      return 0 == n ? Double.NaN : max;
    }

    public double range() {
      return 0 == n ? Double.NaN : max - min;
    }

    public String toString() {
      return getClass().getName() + " of {n:" + n + ",min:" + min + ",max:" + max + "}";
    }
  }
}

abstract class StatDouble extends Stat {
  double _sum = 0, _sumSq = 0, _min = Double.POSITIVE_INFINITY, _max = Double.NEGATIVE_INFINITY;

  void addValue(double value) {
    _sum += value;
    _sumSq += value * value;
    if (_max < value) _max = value;
    if (_min > value) _min = value;
  }

  abstract void add(double value);

  public void clear() {
    _sum = _sumSq = 0;
    _min = Double.POSITIVE_INFINITY;
    _max = Double.NEGATIVE_INFINITY;
  }

  public void add(double... values) {
    for (final double value : values) add(value);
  }

  public double sum() {
    return _sum;
  }

  public double sumSq() {
    return _sumSq;
  }

  public double min() {
    return 0 == size() ? Double.NaN : _min;
  }

  public double max() {
    return 0 == size() ? Double.NaN : _max;
  }
}

final class MinMax<T extends Comparable<T>> extends java.util.AbstractList<T> {
  private T min, max;

  @Override
  public boolean add(T o) {
    if (null == o) return false;
    if (null == min) {
      min = max = o;
      return true;
    }
    if (min.compareTo(o) > 0) min = o;
    else if (max.compareTo(o) < 0) max = o;
    else return false;
    return true;
  }

  public T get(int index) {
    return 0 == index ? min : max;
  }

  public int size() {
    return 2;
  }

  @Override
  public Object[] toArray() {
    return new Object[]{min, max};
  }
}

interface IStat {
  int size();

  double min();

  double max();

  double range();

  interface Add {
    void add(double value);

    void add(double... value);

    void clear();
  }

  interface Easy extends IStat {
    double sum();

    double mean();

    double sumSq();

    double variance();

    double stdDev();
  }

  interface Hard extends Easy {
    /**
     * @param p 0~1
     * @return percentile value
     */
    double percentile(double p);

    /**
     * @param p 0~100
     * @return percentile value
     */
    double percentile(int p);

    double median();

    double geoMean();

    double[] sorted();

    double[] normalized();
  }
}